SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<victor Regalado>
-- Create date: <04/24/2010>
-- Description:	<Update the audit table every time that the salary of the employee is modified.>
-- =============================================
CREATE TRIGGER update_auditTable 
	ON employee AFTER UPDATE
	AS IF UPDATE(post_merge_salary)
	         IF((SELECT COUNT(*) FROM inserted)=1)
		BEGIN 
			SET NOCOUNT ON;
			DECLARE @name VARCHAR(40)
			DECLARE @last_name VARCHAR(40)
			DECLARE @oldSal money
			DECLARE @newSal money
			DECLARE @increa money
			DECLARE @comp CHAR(4)
			DECLARE @ssn CHAR(9)
			DECLARE @deptNum smallint
			DECLARE @supervizorNum CHAR(9)
			
			select @name=(select emp_first_name from inserted)
			select @last_name=(select emp_last_name from inserted)
			select @oldSal=(select post_merge_salary from deleted)
			select @newSal=(select post_merge_salary from inserted)
			select @increa=ROUND(((@newSal/@oldSal - 1)*100),2)
			select @comp=(select company from inserted)
			select @ssn=(select emp_ssn from inserted)
			select @deptNum=(select emp_dpt_number from inserted)
			select @supervizorNum=(select emp_superssn from inserted)
			
			insert into auditTable values(@name,@last_name,@oldSal,@newSal,@increa,@ssn,@comp,@deptNum,@supervizorNum,GETDATE())
			
		END
GO
